Microsoft Word and Excel Reports
The following steps should only need to be performed once.
If the Developer tab does not display in Word / Excel:
- In Word / Excel, click File>Options>Customize Ribbon.
- With Main Tabs selected from the Customize the Ribbon drop list (1), select the Developer check box (2):
In order to run the macros that we will create with our reports we need to ensure that the Security level in Word / Excel is appropriate:
- On the Developer tab, click Macro Security.
- If it is not already selected, click the radio button for Disable all macros with notification:
With this setting, macros are disabled, but security alerts appear if there are macros present. You enable macros on a case-by-case basis.
Note: You could select Enable all macros in order to avoid the need to enable macros on a case-by-case basis however be aware that there is a security risk in doing so.
- Click OK to close the Trust Center.
When we run a report in EMu, one or more files are generated with the report data. These files hold the data that the third party reporting tools (Crystal Reports, Microsoft Word and Excel for instance) use to generate the report.
What kind of files are generated will depend on the Type of report you are creating:
- EMu 4.3 and earlier: report generation typically makes use of ODBC (Open Database Connectivity) and most report Types will generate one or more .csv files.
- EMu 5.0 onwards: it is also possible to create ADO reports which report directly to an ActiveX Data Objects (ADO) RecordSet object (creating a .xml file) rather than reporting to an ODBC data source (which creates one or more .csv files).
Note: More detail about what type of files are created and where they are located is available here.
Read on for details about:
When mail merge facilities were introduced to Microsoft Word, the source of the data for merging in the document was a simple text file. Users were required to edit a text file and add data, one entry per line for each mail merge record required. To run the mail merge, Word needed to know the exact location of the text file that contained the mail merge records. It stored this location in the master merge document so that it could regenerate the merged document when required.
With the introduction of ODBC by Microsoft, the data source is hidden behind a name (DSN) that programs such as Word and Excel can use without needing to know the location of the data source. When creating a report in MS Word or Excel in versions prior to Office 2013 it is important that the DSN is specified using Visual Basic code.
In versions of MS Word and Excel from Office 2013 onwards it is not necessary to specify the DSN using Visual Basic code and the DSN can be specified using a Wizard.
This table provides details of EMu DSN names and the name of the .csv files generated when a report is run out of EMu:
Module Name |
DSN |
.csv File |
Link Column |
---|---|---|---|
Accession Lots |
EMu Accession Lots |
eaccessi.csv |
eaccessionlots_key |
Admin Tasks |
EMu Admin Tasks |
eadmin.csv |
eadmin_key |
Audit Trails |
EMu Audit Trails |
eaudit.csv |
eaudit_key |
Bibliography |
EMu Bibliography |
ebibliog.csv |
ebibliography_key |
Catalogue |
EMu Catalogue |
ecatalog.csv |
ecatalogue_key |
Collection Events |
EMu Collection Events |
ecollect.csv |
ecollectionevents_key |
Condition Checks |
EMu Condition Checks |
econditi.csv |
econdition_key |
Conservation |
EMu Conservation |
econserv.csv |
econservation_key |
Events |
EMu Events |
eevents.csv |
eevents_key |
Gazetteer |
EMu Gazetteer |
egazette.csv |
egazetteer_key |
Groups |
EMu Groups |
egroups.csv |
egroups_key |
Insurance |
EMu Insurance |
einsuran.csv |
einsurance_key |
Internal Movements |
EMu Internal Movements |
einterna.csv |
einternal_key |
Loans |
EMu Loans |
eloans.csv |
eloans_key |
Locations |
EMu Locations |
elocatio.csv |
elocations_key |
Lookup Lists |
EMu Lookup Lists |
eluts.csv |
eluts_key |
Movements |
EMu Movements |
emovemen.csv |
emovements_key |
Multimedia |
EMu Multimedia |
emultime.csv |
emultimedia_key |
Narratives |
EMu Narratives |
enarrati.csv |
enarratives_key |
Parties |
EMu Parties |
eparties.csv |
eparties_key |
Propagation |
EMu Propagation |
epropaga.csv |
epropagation_key |
Registry |
EMu Registry |
eregistr.csv |
eregistry_key |
Rights |
EMu Rights |
erights.csv |
erights_key |
Sites |
EMu Sites |
esites.csv |
esites_key |
Statistics |
EMu Statistics |
estatist.csv |
estatistics_key |
Task Templates |
EMu Task Templates |
etemplat.csv |
etemplate_key |
Taxonomy |
EMu Taxonomy |
etaxonom.csv |
etaxonomy_key |
Thesaurus |
EMu Thesaurus |
ethesaur.csv |
ethesaurus_key |
Transactions |
EMu Transactions |
etransac.csv |
etransactions_key |
Valuations |
EMu Valuations |
evaluati.csv |
evaluations_key |
Voucher Payments |
EMu Voucher Payments |
evoucher.csv |
evoucher_key |
DSNs are stored (and can be modified) in the ODBC Data Source Administrator, which can be accessed by selecting:
- Windows 7: Start>All Programs>Administrative Tools>Data Sources (ODBC)
- Windows 8: Control Panel>System and Security>Administrative Tools>ODBC Data Sources
-
Windows 8/10: Control Panel>System and Security>Administrative Tools>ODBC Data Sources (32-bit)
-
Windows 8.1: Control Panel >Administrative Tools>ODBC Data Sources (32-bit)
To connect to an ADO RecordSet when building a Microsoft ADO Report it is necessary to ensure that Word and Excel are configured to use the Microsoft ActiveX Data Objects Library, and to specify the link to the ADO RecordSet:
- On the Developer tab in Excel, click Visual Basic.
Microsoft Visual Basic for Applications will open.
Tip: If the Developer tab does not display, ensure that Excel has been set up correctly.
- Select Tools>References from the Menu bar.
The References - VBAProject box displays.
- If the checkbox for Microsoft ActiveX Data Objects 6.1 Library is not ticked, click the checkbox:
- Click OK.
When writing the Visual Basic code for your report it is necessary to specify the location of the xmldata file generated by EMu.
The location of this file can vary, but typically it can be found in:
C:\Users\[your username]\AppData\Local\KESoftware\Reports\e[module name]
For example, a report run in the Parties module, will save the xmldata file to:
C:\Users\[your username]\AppData\Local\KESoftware\Reports\eparties
Note: More details here.
Things to note:
- The xmldata file is always called
xmldata.xml
. - xmldata.xml is located in a folder named after the module in which the report was run. In the following example, the report was run in the Parties module and xmldata.xml is located in a folder called eparties:
The following needs to be added to your VB code (be sure to specify the correct module folder):
'Get the persisted record set
source = Environ("LocalAppData") & "\KESoftware\Reports\eparties\xmldata.xml"
Set RecordSet = New ADODB.RecordSet
RecordSet.Open source, "Provider=MSPersist"
Tip: Example code for an Excel ADO Report is provided here.